/**
 *    TimedProfiles - let Android perform tasks based on conditions
 *    Copyright (C) 2011  Daniel Weinhold
 *
 *    This program is free software: you can redistribute it and/or modify
 *    it under the terms of the GNU General Public License as published by
 *    the Free Software Foundation, either version 3 of the License, or
 *    (at your option) any later version.
 *
 *    This program is distributed in the hope that it will be useful,
 *    but WITHOUT ANY WARRANTY; without even the implied warranty of
 *    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 *    GNU General Public License for more details.
 *
 *    You should have received a copy of the GNU General Public License
 *    along with this program.  If not, see <http://www.gnu.org/licenses/>.
 * 
 * */
package de.drak.Profiles;

import java.util.ArrayList;
import java.util.Iterator;

import org.json.JSONArray;
import org.json.JSONException;
import org.json.JSONObject;

import android.content.ComponentName;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.os.Bundle;


public class Database {

    private DatabaseHelper mDbHelper;
    private SQLiteDatabase mDb;

    private static final String DATABASE_NAME = "data";
    private static final int DATABASE_VERSION = 4;
	//PlugInState
    /**Name of Table that stores states of plugins*/
								private static final String DATABASE_TABLE = "storeNforward";
	/**Plugin ID*/				public static final String KEY_ROWID = "_id";							
    /**program package*/		public static final String KEY_COMP = "app_title";
    /**class of edit activity*/	public static final String KEY_EDIT_CLASS = "app_e_class";	
    /**class of receiver*/		public static final String KEY_DO_CLASS = "app_do_class";
    /**human readable string describing KEY_BUNDLE*/
    							public static final String KEY_INFO = "app_info";
    /**holds a serialized Bundle*/
    							public static final String KEY_BUNDLE = "app_bundle";
    
    //profile_settings
    /**apply this setting on activation or deactivation*/
    								public static final String PS_KEY_ON = "on_off";
    /**link to P_DATABASE_TABLE*/	public static final String PS_KEY_PROFILE = "profile_id";
    /**link to DATABASE_TABLE*/		public static final String PS_KEY_PLUGIN = "plugin_id";
    /**Setting ID*/					public static final String PS_KEY_ROWID = "_id";
    /**Name of Table that binds Setting Plugins to Profiles*/
    								private static final String PS_DATABASE_TABLE = "profile_settings";
    //profiles
    /**Profile is active*/			public static final String P_KEY_ACTIVE = "now_akt";
    /**Name of Profile*/			public static final String P_KEY_NAME = "pl_name";
    /**Icon for Profile*/			public static final String P_KEY_ICON = "pl_icon";
    /**Profile ID*/					public static final String P_KEY_ROWID = "_id";
    /**Name of Table that holds all Profiles*/
    								private static final String P_DATABASE_TABLE = "profiles";
    //rule_conditions
    /**Link to R_DATABASE_TABLE*/	public static final String RC_KEY_RULE = "rule";
    /**Link to DATABASE_TABLE*/		public static final String RC_KEY_PLUGIN = "plugin_id";
    /**Condition ID*/				public static final String RC_KEY_ROWID = "idRule_Contions";
    /**Name of Table that binds Condition Plugins to Rules*/
    								private static final String RC_DATABASE_TABLE = "Rule_Contions";
    //rules
    /**rule shall be checked on R_KEY_NEXT or not*/
    								public static final String R_KEY_ACTIVE = "Aktive";
    /**Name of Rule*/				public static final String R_KEY_NAME = "Name";
    /**Icon of Rule*/				public static final String R_KEY_ICON = "icon";
    /**time of next check or 0 (periodicly) or -1 (only check on query from plugin)*/
    								public static final String R_KEY_NEXT = "next_check";
    /**rule is active?*/		    public static final String R_KEY_ISON = "aktive_on_last_check";
    /**Rule ID*/					public static final String R_KEY_ROWID = "idRules";
    /**Name of Table that holds all Rules*/
    								private static final String R_DATABASE_TABLE = "rules";
    //rule_tasks
    /**Link to R_DATABASE_TABLE*/	public static final String RT_KEY_RULE = "rule";
    /**one of enum RULE_TASKS*/		public static final String RT_KEY_DO = "action";
    /**parameter for picked RULE_TASK (RT_KEY_DO)*/
    								public static final String RT_KEY_INK = "action_profile";
    /**Task ID*/
    public static final String RT_KEY_ROWID = "idRule_Tasks";
    /**Name of Table that binds Tasks to Rules*/
    								private static final String RT_DATABASE_TABLE = "Rule_Tasks";

    private final Context mCtx;
    
    /**Tasks that can be performed by a Rule*/
    public static enum RULE_TASKS
    {
    	BIND_PROFILE,
    	NOTIFY,
    	LAUNCH_APP,
    	START_PROFILE,
    	END_PROFILE,
    	DISCARD_ALL_PROFILES,
    	DISABLE_SCANNING
    }

    private static class DatabaseHelper extends SQLiteOpenHelper {

        DatabaseHelper(Context context) {
            super(context, DATABASE_NAME, null, DATABASE_VERSION);
        }

        @Override
        public void onCreate(SQLiteDatabase db) {

            db.execSQL("create table "+DATABASE_TABLE+" ("+KEY_ROWID+" integer primary key autoincrement, "
                    + KEY_COMP +" text not null, "
                    + KEY_EDIT_CLASS +" text, "
                    + KEY_DO_CLASS +" text, "
                    + KEY_INFO +" text not null, "
                    + KEY_BUNDLE + " text not null);");
                 //   + KEY_BUNDLE + " blob not null);");
            db.execSQL("create table "+P_DATABASE_TABLE+" ("+P_KEY_ROWID+" integer primary key autoincrement, "
                    + P_KEY_NAME +" text not null, "
                    + P_KEY_ICON +" INTEGER not null, "
                    + P_KEY_ACTIVE + " INTEGER not null);");
            db.execSQL("create table "+PS_DATABASE_TABLE+" ("+PS_KEY_ROWID+" integer primary key autoincrement, "
                    + PS_KEY_PROFILE +" INTEGER not null, "
                    + PS_KEY_PLUGIN +" INTEGER not null, "
                    + PS_KEY_ON + " INTEGER not null);");
            db.execSQL("create table "+RC_DATABASE_TABLE+" ("+RC_KEY_ROWID+" integer primary key autoincrement, "
                    + RC_KEY_RULE +" INTEGER not null, "
                    + RC_KEY_PLUGIN +" INTEGER not null);");
            db.execSQL("create table "+R_DATABASE_TABLE+" ("+R_KEY_ROWID+" integer primary key autoincrement, "
                    + R_KEY_NAME +" text not null, "
                    + R_KEY_ACTIVE +" INTEGER not null, "
                    + R_KEY_ICON +" INTEGER not null, "
                    + R_KEY_ISON +" INTEGER not null, "
                    + R_KEY_NEXT +" INTEGER not null);");
            db.execSQL("create table "+RT_DATABASE_TABLE+" ("+RT_KEY_ROWID+" integer primary key autoincrement, "
                    + RT_KEY_RULE +" INTEGER not null, "
                    + RT_KEY_DO +" INTEGER not null, "
                    + RT_KEY_INK +" INTEGER not null);");
        }

        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
            //Log.w("TimesProfsDB", "Upgrading database from version " + oldVersion + " to " + newVersion + ", which will destroy all old data");
        	if(oldVersion==3 && newVersion==4)
        	{
                db.execSQL("update "+RT_DATABASE_TABLE+" set "+RT_KEY_DO+" = "+RT_KEY_DO+"+3;");
                db.execSQL("update "+RT_DATABASE_TABLE+" set "+RT_KEY_DO+" = 0 where "+RT_KEY_DO+"=8;");
                db.execSQL("update "+RT_DATABASE_TABLE+" set "+RT_KEY_DO+" = 1 where "+RT_KEY_DO+"=7;");
                db.execSQL("update "+RT_DATABASE_TABLE+" set "+RT_KEY_DO+" = 2 where "+RT_KEY_DO+"=6;");
                db.execSQL("update "+RT_DATABASE_TABLE+" set "+RT_KEY_DO+" = 6 where "+RT_KEY_DO+"=9;");
        	}else{
	        	db.execSQL("DROP TABLE IF EXISTS "+DATABASE_TABLE);
	            db.execSQL("DROP TABLE IF EXISTS "+P_DATABASE_TABLE);
	            db.execSQL("DROP TABLE IF EXISTS "+PS_DATABASE_TABLE);
	            db.execSQL("DROP TABLE IF EXISTS "+RC_DATABASE_TABLE);
	            db.execSQL("DROP TABLE IF EXISTS "+R_DATABASE_TABLE);
	            db.execSQL("DROP TABLE IF EXISTS "+RT_DATABASE_TABLE);
	            onCreate(db);
        	}
        }
    }
    
    /**
     * Constructor - takes the context to allow the database to be
     * opened/created
     * 
     * @param ctx the Context within which to work
     */
    public Database(Context ctx) {
        this.mCtx = ctx;
        mDbHelper = null;
    }

    /**
     * Open the notes database. If it cannot be opened, try to create a new
     * instance of the database. If it cannot be created, throw an exception to
     * signal the failure
     * 
     * @return this (self reference, allowing this to be chained in an
     *         initialization call)
     * @throws SQLException if the database could be neither opened or created
     */
    public Database open() throws SQLException {
    	/*if(mDbHelper != null)
    	{
    		//is open
    		return this;
    	}*/
        mDbHelper = new DatabaseHelper(mCtx);
        mDb = mDbHelper.getWritableDatabase();
        return this;
    }
    
    public void close() {
    	/*if(mDbHelper == null)
    	{
    		//is not open
    		return;
    	}*/
    	mDbHelper.close();
    }

    /*************************************************************PluginState**********************************************************/
    
    /**Tun a Bundle into a String
     * 
     * Supports
     * * Bundles
     * * Arrays of int, byte, short, char, float, long, double, boolean
     * * Arrays of String
     * * ArrayList
     * * int, byte, short, char, float, long, double, boolean
     * */
    @SuppressWarnings("unchecked")
	private String serialize(Bundle b)
    {
    	if(b==null)
    	{
    		return "";
    	}
    	JSONObject res = new JSONObject();
		try {
			for(String key : b.keySet())
	    	{
				if(b.get(key)==null)
				{
					//Log.v("TimesProfsDB", key+" is null");
					continue;
				}
				
				
				if(b.get(key).getClass()==Bundle.class)
	    		{
					//serilize bundle in bundle
	    			res.put(key, "b"+serialize(b.getBundle(key)));
	    		}else if(b.get(key).getClass()==int[].class
	    				|| b.get(key).getClass()==byte[].class
	    				|| b.get(key).getClass()==short[].class
	    				|| b.get(key).getClass()==char[].class
	    				|| b.get(key).getClass()==float[].class){

					//serilize array
	    			JSONArray arr = new JSONArray();
	    			for(int o : b.getIntArray(key))
	    			{
	    				arr.put(o);
	    			}
	    			res.put(""+key, "i"+arr.toString());
	    			
	    		}else if(b.get(key).getClass()==long[].class
	    				|| b.get(key).getClass()==double[].class){

					//serilize array
	    			JSONArray arr = new JSONArray();
	    			for(long o : b.getLongArray(key))
	    			{
	    				arr.put(o);
	    			}
	    			res.put(key, "l"+arr.toString());
	    			
	    		}else if(b.get(key).getClass()==boolean[].class){

					//serilize array
	    			JSONArray arr = new JSONArray();
	    			for(boolean o : b.getBooleanArray(key))
	    			{
	    				arr.put(o);
	    			}
	    			res.put(key, "b"+arr.toString());
	    			
	    		}else if(b.get(key).getClass()==String[].class){

					//serilize array
	    			JSONArray arr = new JSONArray();
	    			for(String o : b.getStringArray(key))
	    			{
	    				arr.put(o);
	    			}
	    			res.put(key, "s"+arr.toString());
	    		
	    		}else if(b.get(key).getClass()==ArrayList.class){

	    			ArrayList bla = b.getIntegerArrayList(key);
	    			
	    			if(!bla.isEmpty() && bla.get(0).getClass()!=Integer.class)
	    			{
	    				bla = b.getStringArrayList(key);
	    			}	    			

	    			Iterator<?> it = bla.iterator();
	    			JSONArray arr = new JSONArray();
	    			
	    			while(it.hasNext())
	    			{
	    				Object o = it.next();
	    				arr.put(o);
	    			}
	    			if(!bla.isEmpty() && bla.get(0).getClass()!=Integer.class)
	    			{
		    			res.put(key, "as"+arr.toString());
	    			}else{	    			
	    				res.put(key, "ai"+arr.toString());
	    			}	    			
	    		}else{
	    			res.put(key, b.get(key));
	    		}
	    	}
		} catch (JSONException e) {
			//Log.w("TimesProfsDB", "serialize failed:", e);
			return "";
		}
    	/*Parcel p = Parcel.obtain();
    	p.writeBundle(b);
    	byte[] bla = p.marshall();
    	Log.v("TimesProfsDB", String.format("store plugin bundle - Size: %d _%d", p.dataSize(), bla.length)); //2.2: size is ok
    	return bla;*/
       /* Parcel p = Parcel.obtain();
        b.writeToParcel(p, Parcelable.PARCELABLE_WRITE_RETURN_VALUE);
        return String.copyValueOf(p.marshall());*/
    	return res.toString();
    }
    /**Tun a String into a Bundle
     * 
     * Supports
     * * Bundles
     * * int, long, double, boolean
     * * String
     * * Arrays with
     * * * boolean
     * * * int
     * * * long
     * * * String
     * * ArrayList with
     * * * Integer
     * * * String
     * */
    @SuppressWarnings("unchecked")
	private Bundle deserialize(String s)
    {
    	JSONObject help_me = null;
    	Bundle b = new Bundle();
    	//JSONArray keys = null;
    	Iterator<String> it;
    	try {
			help_me = new JSONObject(s);
			//keys = help_me.names();
			it = help_me.keys();
			String key = null;
			Class<? extends Object> bla = null;
			//for(int x = 0; x < keys.length();++x)
			while(it.hasNext())
			{
				//key = keys.getString(x);
				key = (String) it.next();
				bla = help_me.get(key).getClass();
				if(bla==null)
				{
					//Log.v("TimesProfsDB", key+" is null");
					continue;
				}
				
				
	    		if(bla==Boolean.class)
	    		{
	    			b.putBoolean(key, help_me.getBoolean(key));
	    		}else if(bla==Integer.class)
	    		{
	    			b.putInt(key, help_me.getInt(key));
	    		}else if(bla==Long.class)
	    		{
	    			b.putLong(key, help_me.getLong(key));
	    		}else if(bla==Double.class)//
	    		{
	    			b.putDouble(key, help_me.getDouble(key));
	    		}else if(bla==String.class)
	    		{
	    			String value = help_me.getString(key);
		    		if(value.charAt(0)=='b' &&
		    				value.length()>2 &&
		    				value.charAt(1)=='{')//if(bla==Bundle.class)
		    		{
		    			b.putBundle(key, deserialize(value.substring(1)));
		    		}else if(value.charAt(0)=='b' &&
		    				value.length()>2 &&
		    				value.charAt(1)=='[')//Array
		    		{
		    			JSONArray arr = new JSONArray(value.substring(1)); 
		    			int x = arr.length();
		    			boolean o[] = new boolean[x];
		    			x--;
		    			while(x>=0)
		    			{
		    				o[x] = arr.getBoolean(x);
		    				x--;
		    			}
		    			b.putBooleanArray(key, o);
		    		}else if(value.charAt(0)=='i' &&
		    				value.length()>2 &&
		    				value.charAt(1)=='[')//Array
		    		{
		    			JSONArray arr = new JSONArray(value.substring(1)); 
		    			int x = arr.length();
		    			int o[] = new int[x];
		    			x--;
		    			while(x>=0)
		    			{
		    				o[x] = arr.getInt(x);
		    				x--;
		    			}
		    			b.putIntArray(key, o);
		    		}else if(value.charAt(0)=='l' &&
		    				value.length()>2 &&
		    				value.charAt(1)=='[')//Array
		    		{
		    			JSONArray arr = new JSONArray(value.substring(1)); 
		    			int x = arr.length();
		    			long o[] = new long[x];
		    			x--;
		    			while(x>=0)
		    			{
		    				o[x] = arr.getLong(x);
		    				x--;
		    			}
		    			b.putLongArray(key, o);
		    		}else if(value.charAt(0)=='s' &&
		    				value.length()>2 &&
		    				value.charAt(1)=='[')//Array
		    		{
		    			JSONArray arr = new JSONArray(value.substring(1)); 
		    			int x = arr.length();
		    			String o[] = new String[x];
		    			x--;
		    			while(x>=0)
		    			{
		    				o[x] = arr.getString(x);
		    				x--;
		    			}
		    			b.putStringArray(key, o);
		    		}else if(value.charAt(0)=='a' &&
		    				value.length()>3 &&
		    				value.charAt(2)=='[')//ArrayList
		    		{
		    			JSONArray arr = new JSONArray(value.substring(2)); 
		    			int x = arr.length();
	    				ArrayList o = new ArrayList(x);
		    			int c=0;
		    			
		    			if(value.charAt(1)=='s')
		    			{
			    			while(c<x)
			    			{
			    				o.add(c, arr.getString(c));
			    				c++;
			    			}
			    			b.putStringArrayList(key, o);
		    			}else if(value.charAt(1)=='i')
		    			{
			    			while(c<x)
			    			{
			    				o.add(c, arr.getInt(c));
			    				c++;
			    			}
			    			b.putIntegerArrayList(key, o);
		    			}
		    		}else 
		    		{
		    			//String
		    			b.putString(key, help_me.getString(key));
		    		}
	    		}else 
	    		{
	    			//?
	    			//Log.i("TimesProfsDB", key+" is "+bla.getName());
	    		}
					
				
			}
		} catch (JSONException e) {
			//Log.w("TimesProfsDB", "deserialize failed:", e);
			return null;
		}
    	/*Parcel p = Parcel.obtain();
        p.unmarshall(s.getBytes(), 0, s.getBytes().length);
        Log.v("TimesProfsDB", String.format("fetching plugin bundle - Size: %d _%d", p.dataSize(), s.getBytes().length)); //2.2: size is ok
    	return p.readBundle();  */
        /*Parcel p = Parcel.obtain();
        p.unmarshall(s.getBytes(), 0, s.getBytes().length);
        //Log.v("TimesProfsDB", String.format("fetching plugin bundle - Size: %d", p.dataSize())); //2.2: size is ok
        
        Bundle a = new Bundle();
        a.readFromParcel(p);//this it dead for 2.x
        return a;*/
    	return b;
    }
    
    /**
     * Insert a new Plugin
     * 
     * @param pack Title of Package
     * @param edit Title of Edit Activity
     * @param doit Title of Fire or Query Receiver
     * @return row ID of the newly inserted PluginState, or -1 if an error occurred
     */
    private long createPluginState(String pack, String edit, String doit) {
        ContentValues initialValues = new ContentValues();
        initialValues.put(KEY_COMP, pack);
        initialValues.put(KEY_EDIT_CLASS, edit);
        initialValues.put(KEY_DO_CLASS, doit);
        initialValues.put(KEY_INFO, "-");
        initialValues.put(KEY_BUNDLE, serialize(null));

        return mDb.insert(DATABASE_TABLE, null, initialValues);
    }

    /**
     * Update the note with the given rowId
     * 
     * @param rowId id of note
     * @param info Status message of PlugIn
     * @param app_bundle Bundle of PlugIn
     * @return true if deleted, false otherwise
     */
    public boolean updatePluginState(long rowId, String info, Bundle app_bundle) {
        ContentValues args = new ContentValues();
        args.put(KEY_INFO, info);
        
        args.put(KEY_BUNDLE, serialize(app_bundle));/*
        
        String res = serialize(app_bundle);
        
        args.put(KEY_BUNDLE, res);

		Log.v("TimesProfsDB", res);
		Log.v("TimesProfsDB", serialize(deserialize(res)));//*/
        
        return mDb.update(DATABASE_TABLE, args, KEY_ROWID + "=" + rowId, null) > 0;
    }
    
    /**
     * Delete the Plugin with the given rowId
     * 
     * @param rowId id of Plugin to delete
     * @return true if deleted, false otherwise
     */
    public boolean deletePluginState(long rowId) {

        return mDb.delete(DATABASE_TABLE, KEY_ROWID + "=" + rowId, null) > 0;
    }
    /**
     * Select Bundle from the PlugIn with the given rowId
     * 
     * @param rowId id of note
     * @return true if deleted, false otherwise
     */
    public Bundle fetchPluginStateBundle(long rowId) throws SQLException {

        Cursor mCursor =  mDb.query(false, DATABASE_TABLE, new String[] {KEY_BUNDLE}, KEY_ROWID + "=" + rowId, null,
                        null, null, null, null);
        if (mCursor != null) {
            mCursor.moveToFirst();
            String str = mCursor.getString(mCursor.getColumnIndex(KEY_BUNDLE));
            //byte[] str = mCursor.getBlob(mCursor.getColumnIndex(KEY_BUNDLE));
            mCursor.close();
            
            return deserialize(str);
        }
        return null;

    }
    /**
     * Select Component from the PlugIn with the given rowId
     * 
     * @param rowId id of note
     * @return true if deleted, false otherwise
     */
    public ComponentName fetchPluginStateComponent(long rowId) throws SQLException {

        Cursor mCursor =  mDb.query(false, DATABASE_TABLE, new String[] {KEY_COMP, KEY_EDIT_CLASS}, KEY_ROWID + "=" + rowId, null,
                        null, null, null, null);
        if (mCursor != null) {
            mCursor.moveToFirst();
            String pack = mCursor.getString(mCursor.getColumnIndex(KEY_COMP));
            String classn = mCursor.getString(mCursor.getColumnIndex(KEY_EDIT_CLASS));//pack+"."+classname
            mCursor.close();
            return new ComponentName(pack, classn);
        }
        return null;

    }
    /**
     * Select Component from the PlugIn with the given rowId
     * 
     * @param rowId id of note
     * @return true if deleted, false otherwise
     */
    public ComponentName fetchPluginStateFireComponent(long rowId) throws SQLException {

        Cursor mCursor =  mDb.query(false, DATABASE_TABLE, new String[] {KEY_COMP, KEY_DO_CLASS}, KEY_ROWID + "=" + rowId, null,
                        null, null, null, null);
        if (mCursor != null) {
            mCursor.moveToFirst();
            String pack = mCursor.getString(mCursor.getColumnIndex(KEY_COMP));
            String classn = mCursor.getString(mCursor.getColumnIndex(KEY_DO_CLASS));//pack+"."+classname
            mCursor.close();
            return new ComponentName(pack, classn);
        }
        return null;

    }
    /**
     * Return a Cursor for a plugin linked to a ProfileSetting
     * 
     * @param ProfileID id of Parent ProfileSetting
     * @return Cursor over plugin
     */
    public Cursor fetchProfileSettingsPluginState(long ProfileSettingID) {
    	
    	Cursor buff = mDb.rawQuery("SELECT "+DATABASE_TABLE+".* FROM "+PS_DATABASE_TABLE +
    			" LEFT JOIN " + DATABASE_TABLE +" ON "+DATABASE_TABLE+"."+KEY_ROWID+"="+PS_KEY_PLUGIN+
    			" WHERE "+PS_DATABASE_TABLE + "."+PS_KEY_ROWID + "=" + ProfileSettingID, null);
		if(buff==null)
		{
			return null;
		}
		if(buff.moveToFirst()==Boolean.FALSE)
		{
			buff.close();
			return null;
		}
		return buff;
    }
    /**
     * Return a Cursor for a plugin linked to a RuleCondition
     * 
     * @param ProfileID id of Parent ProfileSetting
     * @return Cursor over plugin
     */
    public Cursor fetchRuleConditionPluginState(long RuleConditionID) {
    	
    	Cursor buff = mDb.rawQuery("SELECT "+DATABASE_TABLE+".* FROM "+RC_DATABASE_TABLE +
    			" LEFT JOIN " + DATABASE_TABLE +" ON "+DATABASE_TABLE+"."+KEY_ROWID+"="+RC_KEY_PLUGIN+
    			" WHERE "+RC_DATABASE_TABLE + "."+RC_KEY_ROWID + "=" + RuleConditionID, null);
		if(buff==null)
		{
			return null;
		}
		if(buff.moveToFirst()==Boolean.FALSE)
		{
			buff.close();
			return null;
		}
		return buff;
    }
    /**
     * Delete the Plugin with the given package name.
     * Also deletes linked RuleConditions and profilesetings
     * 
     * @param packname package name to delete
     * @return true if deleted, false otherwise
     */    
    public boolean removePlugin(String packname)
    {
    	//profil settings
    	mDb.execSQL("DELETE FROM "+PS_DATABASE_TABLE+" WHERE EXISTS (SELECT * FROM "+DATABASE_TABLE+" WHERE "+KEY_COMP + "= ? AND "+DATABASE_TABLE+"."+KEY_ROWID+"="+PS_KEY_PLUGIN+")", new String[] { packname });
    	//rule conditions
    	mDb.execSQL("DELETE FROM "+RC_DATABASE_TABLE+" WHERE EXISTS (SELECT * FROM "+DATABASE_TABLE+" WHERE "+KEY_COMP + "= ? AND "+DATABASE_TABLE+"."+KEY_ROWID+"="+RC_KEY_PLUGIN+")", new String[] { packname });
    	//plugins an sich
    	return mDb.delete(DATABASE_TABLE, KEY_COMP + "= ?", new String[] { packname }) > 0;
    }
    /*************************************************************Profiles**********************************************************/
    /**
     * Insert a new Profile Setting
     * 
     * @param name name of Profile
     * @param on_off Profile running or not
     * @return row ID of the newly inserted Plugin, or -1 if an error occurred
     */
    public long createProfile(String name, long on_off) {
        ContentValues initialValues = new ContentValues();
        initialValues.put(P_KEY_NAME, name);
        initialValues.put(P_KEY_ACTIVE, on_off);
        initialValues.put(P_KEY_ICON, 0);

        return mDb.insert(P_DATABASE_TABLE, null, initialValues);
    }

    /**
     * Update the note with the given rowId
     * 
     * @param rowId id of Profile
     * @param title Title of Profile (null for dont change)
     * @param on_off Profile running or not (-1 for dont change)
     * @return true if deleted, false otherwise
     */
    public boolean updateProfile(long rowId, String name, long on_off, int icon) {
        ContentValues args = new ContentValues();
        if(name!=null)
        {
        	args.put(P_KEY_NAME, name);
        }
        if(on_off!=-1)
        {
        	args.put(P_KEY_ACTIVE, on_off);
        }
        if(icon!=-1)
        {
        	args.put(P_KEY_ICON, icon);
        }

        return mDb.update(P_DATABASE_TABLE, args, P_KEY_ROWID + "=" + rowId, null) > 0;
    }
    /**
     * Return a Cursor for a Profile
     * 
     * @param ProfileID id of Profile
     * @return Cursor over Profile
     */
    public Cursor fetchProfile(long ProfileID) {

    	Cursor buff = mDb.query(P_DATABASE_TABLE, new String[] {P_KEY_ICON, P_KEY_NAME, P_KEY_ACTIVE}, P_KEY_ROWID + "=" + ProfileID, null, null, null, null); 
		if(buff==null)
		{
			return null;
		}
		if(buff.moveToFirst()==Boolean.FALSE)
		{
			buff.close();
			return null;
		}
		return buff;
    }
    /**
     * Return a Cursor over the list of all Profiles in the database
     * 
     * @return Cursor over all notes
     */
    public Cursor fetchAllProfiles() {

    	Cursor buff = mDb.query(P_DATABASE_TABLE, new String[] {P_KEY_ROWID, P_KEY_ICON, P_KEY_NAME, P_KEY_ACTIVE}, null, null, null, null, null);
		if(buff==null)
		{
			return null;
		}
		if(buff.moveToFirst()==Boolean.FALSE)
		{
			buff.close();
			return null;
		}
		return buff;
    }
    /**
     * discard all Profiles
     * 
     * @return true if done, false otherwise
     */
    public boolean discardProfiles() {
        ContentValues args = new ContentValues();
       	args.put(P_KEY_ACTIVE, 0);

        return mDb.update(P_DATABASE_TABLE, args, null, null) > 0;
    }
    /**
     * Delete the Profile with the given rowId.
     * Also deletes linked ProfileSettings and their Plugins
     * 
     * @param rowId id of note to delete
     * @return true if deleted, false otherwise
     */
    public boolean deleteProfile(long rowId) {
    	mDb.execSQL("DELETE FROM "+DATABASE_TABLE+" WHERE EXISTS (SELECT * FROM "+PS_DATABASE_TABLE+" WHERE "+PS_KEY_PROFILE + "=" + rowId+" AND "+DATABASE_TABLE+"."+KEY_ROWID+"="+PS_KEY_PLUGIN+")");
    	mDb.delete(PS_DATABASE_TABLE, PS_KEY_PROFILE + "=" + rowId, null);
        return mDb.delete(P_DATABASE_TABLE, P_KEY_ROWID + "=" + rowId, null) > 0;
    }
    /*************************************************************ProfileSettings**********************************************************/
    /**
     * Insert a new Profile Setting
     * 
     * @param ProfileID id of Parent Profile
     * @param on_off Do on Profile activate oder dismiss
     * @param newPackage Title of Package
     * @param newEditor Title of Edit Activity
     * @param rec Title of Fire Receiver
     * @return array
     * 				0: ID of the newly inserted ProfileSetting, or -1 if an error occurred
     * 				1: ID of the newly inserted PluginState, or -1 if an error occurred
     */
    public long[] createProfileSetting(long ProfileID, long on_off, String newPackage, String newEditor, String rec) {
    	long ret[] = {-1,-1};
        ContentValues initialValues = new ContentValues();
        initialValues.put(PS_KEY_PROFILE, ProfileID);
        ret[1] = this.createPluginState(newPackage, newEditor, rec); 
        initialValues.put(PS_KEY_PLUGIN, ret[1]);
        initialValues.put(PS_KEY_ON, on_off);

        ret[0] = mDb.insert(PS_DATABASE_TABLE, null, initialValues);
        return ret; 
    }
    /**
     * Return a Cursor over the list of all ProfileSettings
     * 
     * @param ProfileID id of Parent Profile
     * @return Cursor over all notes
     */
    public Cursor fetchProfileSettings(long ProfileID) {
    	
    	Cursor buff = mDb.rawQuery("SELECT "+PS_DATABASE_TABLE+".*, "+DATABASE_TABLE+"."+KEY_INFO+" FROM "+PS_DATABASE_TABLE +
    			" LEFT JOIN " + DATABASE_TABLE +" ON "+DATABASE_TABLE+"."+KEY_ROWID+"="+PS_KEY_PLUGIN+
    			" WHERE "+PS_KEY_PROFILE + "=" + ProfileID, null);
    	//+" ORDER BY "+PS_DATABASE_TABLE+"."+PS_KEY_ON
		if(buff==null)
		{
			return null;
		}
		if(buff.moveToFirst()==Boolean.FALSE)
		{
			buff.close();
			return null;
		}
		return buff;
    }
    /**
     * Return ID of the Unset Bundle of given Settings Plugin
     * 
     * @param ProfileSettingID id of Parent Profile
     * @return Cursor over all notes
     */
    public Long fetchUnsetProfileSettings(long ProfileSettingID) {
    	Long id = 0L;
    	Cursor buff = mDb.rawQuery("SELECT unsetBundle."+KEY_ROWID+" "+
    			"FROM "+PS_DATABASE_TABLE + " AS seta "+
    			"LEFT JOIN "+DATABASE_TABLE+" AS setBundle ON setBundle."+KEY_ROWID+" = seta."+PS_KEY_PLUGIN+" "+
    			"LEFT JOIN "+PS_DATABASE_TABLE + " AS unseta ON seta."+PS_KEY_PROFILE+" = unseta."+PS_KEY_PROFILE+" AND unseta."+PS_KEY_ON+"=0 "+
    			"LEFT JOIN "+DATABASE_TABLE+" AS unsetBundle ON unsetBundle."+KEY_ROWID+" = unseta."+PS_KEY_PLUGIN+" "+
    			"AND unsetBundle."+KEY_COMP+" = setBundle."+KEY_COMP+" AND unsetBundle."+KEY_EDIT_CLASS+" = setBundle."+KEY_EDIT_CLASS+" AND unsetBundle."+KEY_DO_CLASS+" = setBundle."+KEY_DO_CLASS+" "+
    			"WHERE seta."+PS_KEY_ROWID+"="+ProfileSettingID+" "+
    			"AND unsetBundle."+KEY_ROWID+" IS NOT NULL", null);
		if(buff==null)
		{
			return 0L;
		}
		if(buff.getCount()!=1)
		{
			buff.close();
			return 0L;
		}
		if(buff.moveToFirst()!=Boolean.FALSE)
		{
			id = buff.getLong(buff.getColumnIndex(KEY_ROWID));
		}
		buff.close();
		return id;
    }
    /**
     * Delete a Setting with its Plugin
     * 
     * @param rowId id of Setting
     * @return true if deleted, false otherwise
     */
    public boolean deleteProfileSetting(long rowId) {
    	mDb.execSQL("DELETE FROM "+DATABASE_TABLE+" WHERE EXISTS (SELECT * FROM "+PS_DATABASE_TABLE+" WHERE "+PS_KEY_ROWID + "=" + rowId+" AND "+DATABASE_TABLE+"."+KEY_ROWID+"="+PS_KEY_PLUGIN+")");
        return mDb.delete(PS_DATABASE_TABLE, PS_KEY_ROWID + "=" + rowId, null) > 0;
    }
    /*************************************************************rule_conditions**********************************************************/
    /**
     * Insert a new Rule Condition
     * 
     * @param Rule id of Parent Rule
     * @param newPackage Title of Package
     * @param newEditor Title of Edit Activity
     * @param rec Title of Query Receiver
     * @return array
     * 				0: ID of the newly inserted RuleCondition, or -1 if an error occurred
     * 				1: ID of the newly inserted PluginState, or -1 if an error occurred
     */
    public long[] createRuleCondition(long Rule, String newPackage, String newEditor, String rec) {
    	long ret[] = {-1,-1};
        ContentValues initialValues = new ContentValues();
        initialValues.put(RC_KEY_RULE, Rule);
        ret[1] = this.createPluginState(newPackage, newEditor, rec); 
        initialValues.put(RC_KEY_PLUGIN, ret[1]);

        ret[0] = mDb.insert(RC_DATABASE_TABLE, null, initialValues);
        return ret; 
    }
    /**
     * Return a Cursor over the list of all RuleConditions
     * 
     * @param Rule id of Parent Rule
     * @return Cursor over all notes
     */
    public Cursor fetchRuleCondition(long Rule) {
    	
    	Cursor buff = mDb.rawQuery("SELECT "+RC_DATABASE_TABLE+".*, "+DATABASE_TABLE+"."+KEY_INFO+" FROM "+RC_DATABASE_TABLE +
    			" LEFT JOIN " + DATABASE_TABLE +" ON "+DATABASE_TABLE+"."+KEY_ROWID+"="+RC_KEY_PLUGIN+
    			" WHERE "+RC_KEY_RULE + "=" + Rule, null);
		if(buff==null)
		{
			return null;
		}
		if(buff.moveToFirst()==Boolean.FALSE)
		{
			buff.close();
			return null;
		}
		return buff;
    }
    /**
     * Delete a Condition with its Plugin
     * 
     * @param rowId id of Condition
     * @return true if deleted, false otherwise
     */
    public boolean deleteRuleCondition(long rowId) {
    	mDb.execSQL("DELETE FROM "+DATABASE_TABLE+" WHERE EXISTS (SELECT * FROM "+RC_DATABASE_TABLE+" WHERE "+RC_KEY_ROWID + "=" + rowId+" AND "+DATABASE_TABLE+"."+KEY_ROWID+"="+RC_KEY_PLUGIN+")");
        return mDb.delete(RC_DATABASE_TABLE, RC_KEY_ROWID + "=" + rowId, null) > 0;
    }
    /*************************************************************Rules**********************************************************/
    /**
     * Insert a new Rule
     * 
     * @param name name of Rule
     * @param on_off Rule running or not
     * @return row ID of the newly inserted Rule, or -1 if an error occurred
     */
    public long createRule(String name, long on_off) {
        ContentValues initialValues = new ContentValues();
        initialValues.put(R_KEY_NAME, name);
        initialValues.put(R_KEY_ACTIVE, on_off);
        initialValues.put(R_KEY_ICON, 0);
        initialValues.put(R_KEY_ISON, 0);
        initialValues.put(R_KEY_NEXT, 0);

        return mDb.insert(R_DATABASE_TABLE, null, initialValues);
    }

    /**
     * Update the Rule with the given rowId
     * 
     * @param rowId id of Rule
     * @param title Title of Rule (null for dont change)
     * @param on_off Rule active or not (-1 for dont change)
     * @param was_on was rule stat true on last check (-1 for dont change)
     * @param next_check Time of next check (-1 for dont change)
     * @param icon resource id (-1 for dont change)
     * @return true if updated, false otherwise
     */
    public boolean updateRule(long rowId, String name, long on_off, long was_on, long next_check, int ico) {
        ContentValues args = new ContentValues();
        if(name!=null)
        {
        	args.put(R_KEY_NAME, name);
        }
        if(on_off!=-1)
        {
        	args.put(R_KEY_ACTIVE, on_off);
        }
        if(was_on!=-1)
        {
        	args.put(R_KEY_ISON, was_on);
        }
        if(next_check!=-1)
        {
        	args.put(R_KEY_NEXT, next_check);
        }
        if(ico!=-1)
        {
        	args.put(R_KEY_ICON, ico);
        }
        if(args.size()<1)
        {
        	return false;
        }

        return mDb.update(R_DATABASE_TABLE, args, R_KEY_ROWID + "=" + rowId, null) > 0;
    }
    /**
     * Return a Cursor for a Rule
     * 
     * @param Rule id of Rule
     * @return Cursor over Rule
     */
    public Cursor fetchRule(long Rule) {

    	Cursor buff = mDb.query(R_DATABASE_TABLE, new String[] {R_KEY_ICON, R_KEY_NAME, R_KEY_ACTIVE}, R_KEY_ROWID + "=" + Rule, null, null, null, null); 
		if(buff==null)
		{
			return null;
		}
		if(buff.moveToFirst()==Boolean.FALSE)
		{
			buff.close();
			return null;
		}
		return buff;
    }
    /**
     * Return a Cursor over the list of all Rules in the database
     * 
     * @return Cursor over all notes
     */
    public Cursor fetchAllRules() {

    	Cursor buff = mDb.query(R_DATABASE_TABLE, new String[] {R_KEY_ROWID, R_KEY_ICON, R_KEY_NAME, R_KEY_ACTIVE}, null, null, null, null, null);
		if(buff==null)
		{
			return null;
		}
		if(buff.moveToFirst()==Boolean.FALSE)
		{
			buff.close();
			return null;
		}
		return buff;
    }
    /**
     * Return a Cursor over the list of all Rules that are active
     * 
     * @return Cursor over all notes
     */
    public Cursor fetchActiveRules() {

    	Cursor buff = mDb.query(R_DATABASE_TABLE, new String[] {R_KEY_ROWID, R_KEY_ISON, R_KEY_NEXT}, R_KEY_ACTIVE+"=1 AND "+R_KEY_NEXT+"=0", null, null, null, null);
		if(buff==null)
		{
			return null;
		}
		if(buff.moveToFirst()==Boolean.FALSE)
		{
			buff.close();
			return null;
		}
		return buff;
    }
    /**
     * Return a Cursor over the list of all Rules that are active and use a Condition with a plugin from packname
     * 
     * @param editClass Name of package containung plugins plus edit activity
     * @return Cursor over all notes
     */    
    public Cursor fetchRules(String editClass)
    {    	
    	Cursor buff = mDb.rawQuery("SELECT "
    			+R_DATABASE_TABLE+"."+R_KEY_ROWID+","
    			+R_DATABASE_TABLE+"."+R_KEY_ISON+","
    			+R_DATABASE_TABLE+"."+R_KEY_NEXT+" FROM "+RC_DATABASE_TABLE
    			+" LEFT JOIN " + R_DATABASE_TABLE +" ON "+R_DATABASE_TABLE+"."+R_KEY_ROWID+"="+RC_KEY_RULE
    			+" WHERE EXISTS (SELECT * FROM "+DATABASE_TABLE
    			+" WHERE "+ /*KEY_COMP + "||"+ */ KEY_EDIT_CLASS + "=? AND "+DATABASE_TABLE+"."+KEY_ROWID+"="+RC_KEY_PLUGIN+")"
    			+" AND "+R_KEY_ACTIVE+"=1", new String[] { editClass });
    	
		if(buff==null)
		{
			return null;
		}
		if(buff.moveToFirst()==Boolean.FALSE)
		{
			buff.close();
			return null;
		}
		return buff;
    }
    /**
     * Return the Rule with the lowest NextCheck value
     * 
     * @return date, 0 if error
     * */
    public Cursor fetchDeadLineRules()
    {
    	Cursor buff = mDb.rawQuery("SELECT "
    			+R_KEY_ROWID+","
    			+R_KEY_ISON+","
    			+R_KEY_NEXT+" FROM "+R_DATABASE_TABLE
    			+" WHERE "+R_KEY_ACTIVE+"=1"
    			+" AND "+R_KEY_NEXT+"!=0"
    			+" ORDER BY "+R_KEY_NEXT, null);
		if(buff==null)
		{
			return null;
		}
		if(buff.moveToFirst()==Boolean.FALSE)
		{
			buff.close();
			return null;
		}
		return buff;
    }
    /**
     * Counts all active Rules
     * 
     * @return array
     * 				0: anz rules with deadline
     * 				1: anz rules without deadline
     * */
    public int[] fetchReadyRules()
    {
    	Cursor mCursor = mDb.rawQuery("SELECT ("+
	    			"SELECT COUNT("+R_KEY_ROWID+") FROM "+R_DATABASE_TABLE
	    			+" WHERE "+R_KEY_ACTIVE+"=1"
	    			+" AND "+R_KEY_NEXT+"!=0"
    			+") as d, ("+
	    			"SELECT COUNT("+R_KEY_ROWID+") FROM "+R_DATABASE_TABLE
	    			+" WHERE "+R_KEY_ACTIVE+"=1"
	    			+" AND "+R_KEY_NEXT+"=0"   			
    			+") as p", null);
        if (mCursor != null) {
    		if(mCursor.moveToFirst()==Boolean.FALSE)
    		{
    			mCursor.close();
    			return new int[]{0,0};
    		}
            int[] r = {mCursor.getInt(0),mCursor.getInt(1)};
            mCursor.close();
            return r;
        }
        return new int[]{0,0};
    }
    /**
     * Return the lowest NextCheck value
     * 
     * @return date, 0 if error
     * */
    public long getNextDeadLine()
    {
    	Cursor mCursor = mDb.rawQuery("SELECT "
    			+R_KEY_NEXT+" FROM "+R_DATABASE_TABLE
    			+" WHERE "+R_KEY_ACTIVE+"=1"
    			+" AND "+R_KEY_NEXT+"!=0"
    			+" ORDER BY "+R_KEY_NEXT
    			+" LIMIT 1", null);
        if (mCursor != null) {
    		if(mCursor.moveToFirst()==Boolean.FALSE)
    		{
    			mCursor.close();
    			return 0;
    		}
            long r = mCursor.getLong(mCursor.getColumnIndex(R_KEY_NEXT));
            mCursor.close();
            return r;
        }
        return 0;
    }
    /**
     * Delete the Rule with the given rowId.
     * Also deletes linked RuleCondition and their Plugins
     * 
     * @param rowId id of note to delete
     * @return true if deleted, false otherwise
     */
    public boolean deleteRule(long rowId) {
    	mDb.execSQL("DELETE FROM "+DATABASE_TABLE+" WHERE EXISTS (SELECT * FROM "+RC_DATABASE_TABLE+" WHERE "+RC_KEY_RULE + "=" + rowId+" AND "+DATABASE_TABLE+"."+KEY_ROWID+"="+RC_KEY_PLUGIN+")");
    	mDb.delete(RC_DATABASE_TABLE, RC_KEY_RULE + "=" + rowId, null);
        return mDb.delete(R_DATABASE_TABLE, R_KEY_ROWID + "=" + rowId, null) > 0;
    }
    /*************************************************************Tasks**********************************************************/
    
    /*
     * Get an RULE_TASK for an Integer
     */
    public static RULE_TASKS IntToTask(int i)
    {
    	return RULE_TASKS.values()[ i ];
    }
    /**
     * Insert a new Rule Task
     * 
     * @param Rule id of Parent Rule
     * @param _do
     * @param ink
     * @return ID of the newly inserted RuleTask, or -1 if an error occurred
     */
    public long createRuleTask(long Rule, RULE_TASKS _do, long ink) {
        ContentValues initialValues = new ContentValues();
        initialValues.put(RT_KEY_RULE, Rule);
        initialValues.put(RT_KEY_DO, _do.ordinal());
        initialValues.put(RT_KEY_INK, ink);

        return mDb.insert(RT_DATABASE_TABLE, null, initialValues);
    }
    
    public boolean updateRuleTask(long rowId, RULE_TASKS _do, long ink) {
        ContentValues args = new ContentValues();
        if(_do!=null)
        {
        	args.put(RT_KEY_DO, _do.ordinal());
        }
        if(ink!=-1)
        {
        	args.put(RT_KEY_INK, ink);
        }

        return mDb.update(RT_DATABASE_TABLE, args, RT_KEY_ROWID + "=" + rowId, null) > 0;
    }
    /**
     * Return a Cursor over the list of all Rule Tasks
     * 
     * @param Rule id of Parent Rule
     * @return Cursor over all notes
     */
    public Cursor fetchRuleTasks(long Rule) {
    	
    	Cursor buff = mDb.rawQuery("SELECT * FROM "+RT_DATABASE_TABLE +
    			" WHERE "+RT_KEY_RULE + "=" + Rule, null);
		if(buff==null)
		{
			return null;
		}
		if(buff.moveToFirst()==Boolean.FALSE)
		{
			buff.close();
			return null;
		}
		return buff;
    }
    /**
     * Return a Cursor for this task
     * 
     * @param Rule id of Parent Rule
     * @return Cursor over all notes
     */
    public Cursor fetchRuleTask(long Task) {
    	
    	Cursor buff = mDb.rawQuery("SELECT * FROM "+RT_DATABASE_TABLE +
    			" WHERE "+RT_KEY_ROWID + "=" + Task, null);
		if(buff==null)
		{
			return null;
		}
		if(buff.moveToFirst()==Boolean.FALSE)
		{
			buff.close();
			return null;
		}
		return buff;
    }
    /**
     * Delete a Task
     * 
     * @param rowId id of Condition
     * @return true if deleted, false otherwise
     */
    public boolean deleteRuleTask(long rowId) {
        return mDb.delete(RT_DATABASE_TABLE, RT_KEY_ROWID + "=" + rowId, null) > 0;
    }
}
